import 'package:activity_record/model/diy_image.dart';
import 'package:activity_record/model/diy_project.dart';
import 'package:sqflite/sqflite.dart';
import 'dart:io';
import 'dart:async';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';

class DataBase {
  Database _myDateBase;
  //定义表名
  final String tableName = "diyTable1";
  //定义字段名
  final String columnId = "id";
  final String columnName = "name";
  final String columnDate = "date";
  final String columnPlace = "place";
  final String columnContact = "contact";
  final String columnSinglePrice = "singlePrice";
  final String columnNums = "nums";
  final String columnTotalAmount = "totalAmount";
  final String columnItemCost = "itemCost";
  final String columnLaborCost = "laborCost";
  final String columnProfit = "profit";
  final String columnUnPaidAmount = "unPaidAmount";
  final String columnisPaided = "isPaided";
  final String columnNotes = "notes";

  final String imageTableName = 'diyImagesTable';
  final String columnImageId = 'imageId';
  final String columnDiyId = 'diyId';
  final String columnImageUrl = "imageUrl";
  //final String columnImageCompressedUrl = "imageCompressedUrl";

  //获取数据库
  Future get db async {
    if (_myDateBase != null) {
      //print('数据库已存在');
      return _myDateBase;
    } else
      _myDateBase = await initDb();
    return _myDateBase;
  }

  //初始化数据库，根据路径版本号新建数据库
  initDb() async {
    Directory directory = await getApplicationDocumentsDirectory();
    String path = join(directory.path, "diyDB.db");
    var dataBase = await openDatabase(path, version: 1, onCreate: _onCreate);
    print('数据库创建成功，version:1');
    print('path: $path');
    return dataBase;
  }

  //新建数据库表
  FutureOr _onCreate(Database db, int version) async {
    await db.execute('''create table $tableName(
    $columnId integer primary key autoincrement,
    $columnName text not null,
    $columnDate text,
    $columnPlace text,
    $columnContact text,
    $columnSinglePrice integer not null,
    $columnNums integer not null,
    $columnTotalAmount integer not null,
    $columnItemCost integer ,
    $columnLaborCost integer ,
    $columnProfit integer not null,
    $columnUnPaidAmount,
    $columnisPaided,
    $columnNotes)''');
    print('$tableName is created');
    await db.execute('''create table $imageTableName(
    $columnImageId integer primary key autoincrement,
    $columnDiyId integer,
    $columnImageUrl String)''');
    print('$imageTableName is created');
  }

  //插入diyProject
  Future<int> insertDiyProject(DiyProject diy) async {
    //获取数据库实例
    Database database = await db;
    //diy.toMap()是将diy实例转换成字段名和值对应的map
    var result = database.insert(tableName, diy.toMap());
    //print('数据已插入');
    return result;
  }

  //根据diy的ID插入diyProject的图片
  Future<int> insertDiyImages(DiyImage diyImage) async {
    //获取数据库实例
    Database database = await db;
    //diy.toMap()是将diy实例转换成字段名和值对应的map
    var result = database.insert(imageTableName, diyImage.toMap());
    //print('图片数据已插入');
    return result;
  }

  //获取所有diyProject
  Future<List> getDiyProjects() async {
    //获取数据库实例
    Database database = await db;
    //返回一个 map型的数组，其中map是由字段名和值构成
    var result = await database
        .rawQuery("select * from $tableName order by $columnId desc");
    //print('获取所有diyProject,当前diyProject有: $result');
    return result;
  }

  //根据diyID获取所有对应图片
  Future<List> getImageDatas(int id) async {
    //获取数据库实例
    Database database = await db;
    //返回一个 map型的数组，其中map是由字段名和值构成
    var result = await database
        .rawQuery("select * from $imageTableName where $columnDiyId = $id");
    //print('获取diy($id)对应的所有图片,当前diyProject图片有${result.length}张: $result');
    return result;
  }

  // //根据diyID获取所有对应缩略图图片
  // Future<List> getImageDatasCompressed(int id) async {
  //   //获取数据库实例
  //   Database database = await db;
  //   //返回一个 map型的数组，其中map是由字段名和值构成
  //   var result = await database.rawQuery(
  //       "select $columnImageDataCompressed from $imageTableName where $columnDiyId = $id");
  //   //print('获取diy($id)对应的所有图片,当前diyProject图片有${result.length}张: $result');
  //   return result;
  // }

  //根据diyID删除所有对应图片
  Future deleteImageDatas(int id) async {
    Database dataBase = await db;
    await dataBase
        .rawDelete("delete from $imageTableName where $columnDiyId = $id");
  }

  //获取diyProject总数
  Future<int> getDiyCount() async {
    Database database = await db;
    var result = await database.rawQuery("select count(*) from $tableName");
    return result[0]['count(*)'];
  }

  //获取未结账的diyProject
  Future<List> getUnCheckedDiyProjects() async {
    Database database = await db;
    var result = await database.rawQuery(
        "select * from $tableName where $columnisPaided = '0' order by $columnId desc");
    return result;
  }

  //获取单个diyProject
  Future<DiyProject> getDiyProject(int id) async {
    Database database = await db;
    //根据id查询对应的diy项目，并返回一个map类型的数组
    var result = await database
        .rawQuery("select * from $tableName where $columnId = $id");
    if (result.length == 0) {
      return null;
    } else
      return DiyProject.fromMap(result[0]);
  }

  //更新diyProject
  Future<int> updateDiyProject(DiyProject diyProject) async {
    Database database = await db;
    var result = await database.update(tableName, diyProject.toMap(),
        where: "$columnId = ?", whereArgs: [diyProject.id]);
    print('我是更新数据的方法 本次更新的res: $result');
    return result;
  }

  //更新是否结算状态
  Future<void> updateChecked(DiyProject diyProject) async {
    Database database = await db;
    print('数据库方法里的结款状态：${diyProject.isPaided}');
    if (diyProject.isPaided) {
      await database.rawUpdate(
          "update $tableName set $columnisPaided = '0' where $columnId = ${diyProject.id}");
    } else
      await database.rawUpdate(
          "update $tableName set $columnisPaided = '1' where $columnId = ${diyProject.id}");
  }

  //删除diyProject
  Future<int> deleteDiyProject(int id) async {
    Database database = await db;
    var result = await database
        .rawDelete("delete from $tableName where $columnId = $id");
    return result;
  }

  //按月分组查询统计项目属性内容
  Future<List> queryAll() async {
    Database database = await db;
    var result = await database.rawQuery(
        "select SUBSTR($columnDate,6,2) as month,count(*) as nums,sum($columnTotalAmount) as sumTotalAmount,sum($columnProfit) as sumProfit,sum($columnUnPaidAmount) as sumUnPaidAmount from $tableName group by SUBSTR($columnDate,6,2) order by SUBSTR($columnDate,6,12) desc");
    print('分组查询结果：$result');
    return result;
  }

  //查询总营收
  Future<int> queryTotalAmount() async {
    Database database = await db;
    var result = await database.rawQuery(
        "select sum($columnTotalAmount) as sumTotalAmount from $tableName");
    return result[0]['sumTotalAmount'];
  }

  //查询总利润
  Future<int> queryProfit() async {
    Database database = await db;
    var result = await database
        .rawQuery("select sum($columnProfit) as sumProfit from $tableName");
    return result[0]['sumProfit'];
  }

  //查询总欠款
  Future<int> queryUnPaid() async {
    Database database = await db;
    var result = await database.rawQuery(
        "select sum($columnUnPaidAmount) as UnPaidAmount from $tableName");
    return result[0]['UnPaidAmount'];
  }

  //根据内容查询数据库name和Contact字段
  Future searchDiy(String value) async {
    Database database = await db;
    var result = database.rawQuery(
        "select * from $tableName where $columnName like '%$value%' or $columnContact like '%$value%'");
    return result;
  }

  //根据年、月查询数据库中共有多少日期
  Future searchDate() async {
    Database database = await db;
    var result = database.rawQuery(
        "select SUBSTR($columnDate,1,7) as date from $tableName group by  SUBSTR($columnDate,1,7) order by SUBSTR($columnDate,6,12) desc");
    return result;
  }

  //关闭数据库
  Future close() async {
    Database database = await db;
    database.close();
  }
}
